package com.iris.live.services.data.repositories.impl;

import com.google.common.collect.Maps;
import com.iris.live.services.common.StringUtils;
import com.iris.live.services.data.repositories.MgtReportRepository;
import com.iris.live.services.models.PageData;
import org.springframework.stereotype.Component;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by HangLiu on 2017/9/21.
 */
@Component()
public class MgtReportRepositoryImpl extends BaseRepository implements MgtReportRepository {
    /**
     * 获取市场成效数据
     * 进店线索数目标	进店线索达成	进店线索满足率	散客线索目标	散客线索达成	呼入线索目标	呼入线索达成	网络线索目标
     * 网络线索达成	主动集客线索目标	主动集客线索达成	推荐线索目标	推荐线索达成	再购线索目标	再购线索达成
     * 售后入厂台次目标	售后入厂台次达成	进场台次满足率	售后新增客户目标	售后新增客户达成
     * @param info
     * @return
     */
    @Override
    public List getMarketEffectList(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.* ,  IFNULL(c.散客客流数,0) aimIndividualClueNum ,  IFNULL(c.呼入线索数,0) aimIncallClueNum,  IFNULL(c.网络线索数,0)  aimNetworkClueNum,\n" +
                "\t\t\t IFNULL(c.推荐线索数,0) aimRecommendClueNum , IFNULL(c.主动集客线索数,0)  aimInitiativeClueNum, IFNULL(c.再购线索数,0) aimRepurchaseClueNum,\n" +
                "\t\t\tIFNULL(c.after_sale_aim_add_customer_num,0)  afterSaleAimAddCustomerNum , \n" +
                "\t\t( IFNULL(c.散客线索留档率*c.散客客流数,0)+IFNULL(c.呼入线索进店率*c.呼入线索数,0)+\n" +
                "\t\t\tIFNULL(c.网络线索进店率*c.网络线索数,0)+IFNULL(c.推荐线索进店率*c.推荐线索数,0)+\n" +
                "\t\t\tIFNULL(c.主动集客线索进店率*c.主动集客线索数,0)+IFNULL(c.再购线索进店率*c.再购线索数,0))  aimInroomClueNum,\t\t\n" +
                "\t\t\tround(IFNULL( a.actualInroomClueNum /( IFNULL(c.散客线索留档率*c.散客客流数,0)+IFNULL(c.呼入线索进店率*c.呼入线索数,0)+\n" +
                "\t\t\tIFNULL(c.网络线索进店率*c.网络线索数,0)+IFNULL(c.推荐线索进店率*c.推荐线索数,0)+\n" +
                "\t\t\tIFNULL(c.主动集客线索进店率*c.主动集客线索数,0)+IFNULL(c.再购线索进店率*c.再购线索数,0)) ,0),4)*100  inroomRate,\n" +
                "\t\t\tROUND(IFNULL(a.actualDeliveryNum/a.actualManufacturerSaleTarget,0),4) *100 futureSaleRate \n" +
                "from\n" +
                "\t\t(\tselect\n" +
                "\t\ta.permit ,a.stat_date ,\n" +
                "\t\tsum(inroom_clue_num)  actualInroomClueNum,\n" +
                "\t\tsum(individual_clue_num) actualIndividualClueNum,\n" +
                "\t\tsum(incall_clue_num) actualIncallClueNum,\n" +
                "\t\tsum(network_clue_num) actualNetworkClueNum,\n" +
                "\t\tsum(initiative_clue_num) actualInitiativeClueNum,\n" +
                "\t\tsum(recommend_clue_num) actualRecommendClueNum,\n" +
                "\t\tsum(repurchase_clue_num) actualRepurchaseClueNum,\n" +
                "\t\tsum(delivery_num) actualDeliveryNum,\n" +
                "\t\tsum(manufacturer_sale_target) actualManufacturerSaleTarget\n" +
                "\t\tfrom  t_kpi_year_monthly_data  a    group by  a.permit ,a.stat_date\n" +
                "\t)a \n" +
                "LEFT JOIN (\n" +
                "\t\t\tselect `月度`, `许可`,\n" +
                "\t\t\t\t\tstr_split(散客线索留档率,'%',1) 散客线索留档率 , str_split(呼入线索进店率,'%',1) 呼入线索进店率, \n" +
                "\t\t\t\t\tstr_split(网络线索进店率,'%',1) 网络线索进店率, str_split(主动集客线索进店率,'%',1) 主动集客线索进店率,\n" +
                "\t\t\t\t\tstr_split(推荐线索进店率,'%',1 )推荐线索进店率, str_split(再购线索进店率,'%',1) 再购线索进店率,\n" +
                "\t\t\t\t\t`散客客流数`,  `呼入线索数` ,  `网络线索数`,  `主动集客线索数`,  `推荐线索数` , `再购线索数` ,after_sale_aim_add_customer_num  \n" +
                "\t\t\tfrom  月度设定目标 a\n" +
                ") c on c.月度=a.stat_date and c.许可=a.permit\n" +
                "where a.permit=:permit and stat_date like :year  order by  stat_date ";
        params.put("permit",info.get("permit"));
        params.put("year",info.get("selectYear")+"%");
        return this.getList(sql, params);
    }

    /**
     * 获取费用数据     *
     *  当月媒体实际支付	当月广宣执行费用	厂家到账支援金	账面支出  当月开展活动数量(个)	当月活动实际支付	当月活动执行费用	厂家到账支援金	账面支出       注：销售 售后各一组
     *  实际支付费用总和	当月市场执行费用总和	厂家支援总和	账面支出总和	已批年度预算	预算费用当月结余	含厂家支援预算结余 市场费用/进店线索	市场费用/进场台次
     * @param info
     * @return
     */
    @Override
    public List getMarketExpenseList(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.permit, a.stat_date, \n" +
                "\t\t-- 销售媒体费用\n" +
                "\t\tIFNULL( b.`sell_media_actually_payment`,0) sellMediaActuallyPayment,\n" +
                "\t\tIFNULL( b.`sell_media_publicity_expenses`,0) sellMediaPublicityExpenses   ,\n" +
                "\t\tIFNULL( b.`sell_media_factory_support_funds`,0) sellMediaFactorySupportFunds ,\n" +
                "\t\t(IFNULL( b.`sell_media_actually_payment`,0)-IFNULL( b.`sell_media_factory_support_funds`,0)\t) sellMediaExpenditure,\n" +
                "\n" +
                "\t\t-- 销售活动费用 \n" +
                "\t\tIFNULL( b.`sell_activities_num`,0) sellActivitiesNum ,\n" +
                "\t\tIFNULL( b.`sell_activitie_payment`,0) sellActivitiePayment ,\n" +
                "\t\tIFNULL( b.`sell_activitie_execute_expenses`,0) sellActivitieExecuteExpenses  ,\n" +
                "\t\tIFNULL( b.`sell_activitie_factory_support_funds`,0) sellActivitieFactorySupportFunds ,\n" +
                "\t\t(IFNULL( b.`sell_activitie_payment`,0)-IFNULL( b.`sell_activitie_factory_support_funds`,0)\t)sellActivitieExpenditure,\n" +
                "\n" +
                "\t\t-- 售后媒体费用\n" +
                "\t\tIFNULL( b.`after_sale_media_actually_payment`,0) afterSaleMediaActuallyPayment ,\n" +
                "\t\tIFNULL( b.`after_sale_media_publicity_expenses`,0)  afterSaleMediaPublicityExpenses ,\n" +
                "\t\tIFNULL( b.`after_sale_media_factory_support_funds`,0) afterSaleMediaFactorySupportFunds ,\n" +
                "\t\t(IFNULL( b.`after_sale_media_actually_payment`,0)-IFNULL( b.`after_sale_media_factory_support_funds`,0)\t) afterSellMediaExpenditure,\n" +
                "\n" +
                "\t\t-- 售后活动费用\n" +
                "\t\tIFNULL( b.`after_sale_activities_num`,0) afterSaleActivitiesNum ,\n" +
                "\t\tIFNULL( b.`after_sale_activitie_payment`,0)  afterSaleActivitiePayment ,\n" +
                "\t\tIFNULL( b.`after_sale_activitie_execute_expenses`,0) afterSaleActivitieExecuteExpenses   ,\n" +
                "\t\tIFNULL( b.`after_sale_activitie_factory_support_funds`,0) afterSaleActivitieFactorySupportFunds,\n" +
                "\t\t(IFNULL( b.`after_sale_activitie_payment`,0)-IFNULL( b.`after_sale_activitie_factory_support_funds`,0)) afterSaleActivitieMediaExpenditure,\t\n" +
                "\t\t-- 总市场费用\t\n" +
                "\t\tIFNULL( b.`sell_media_actually_payment`,0) + IFNULL( b.`sell_activitie_payment`,0)  totalActualPayment,\n" +
                "\t\tIFNULL( b.`sell_media_publicity_expenses`,0) + IFNULL( b.`sell_activitie_execute_expenses`,0)  totalMarketPayment,\n" +
                "\t\tIFNULL( b.`sell_media_factory_support_funds`,0) + IFNULL( b.`sell_activitie_factory_support_funds`,0)  totalFactorySupport,\n" +
                "\t\tIFNULL( b.`sell_media_actually_payment`,0) + IFNULL( b.`sell_activitie_payment`,0) -IFNULL( b.`sell_media_factory_support_funds`,0) - IFNULL( b.`sell_activitie_factory_support_funds`,0) totalExpenditure,\n" +
                "\t\t-- 市场费用效率\n" +
                "\t\tROUND(IFNULL((IFNULL( b.`sell_media_publicity_expenses`,0) + IFNULL( b.`sell_activitie_execute_expenses`,0))/a.inroom_clue_num,0),2)\t marketPaymentInroomClue,\n" +
                "\t\tROUND(IFNULL(((IFNULL( b.`sell_media_publicity_expenses`,0) + IFNULL( b.`sell_activitie_execute_expenses`,0))/a.delivery_num),0),2)\t\t marketPaymentDelivery\n" +
                "from t_market_integrated_report b\n" +
                "LEFT JOIN  (\n" +
                "\t\tselect\n" +
                "\t\ta.permit ,a.stat_date ,\n" +
                "\t\tsum(inroom_clue_num)  inroom_clue_num,\t\n" +
                "\t\tsum(delivery_num) delivery_num\t\t\n" +
                "\t\tfrom  t_kpi_year_monthly_data  a    group by  a.permit ,a.stat_date\n" +
                ") a on a.permit=b.permit and a.stat_date=b.stat_date " +
                "where b.permit=:permit and b.stat_date like :year  order by  b.stat_date ";
        params.put("permit",info.get("permit"));
        params.put("year",info.get("selectYear")+"%");
        return this.getList(sql, params);
    }

    @Override
    public List getMarketAnnualBudget(Map info) {
        Map params = Maps.newHashMap();
        String sql="select* from   t_market_annual_budget  b" +
                " where b.permit=:permit and b.stat_date =:year ";
        params.put("permit",info.get("permit"));
        params.put("year",info.get("selectYear"));
        return this.getList(sql, params);
    }

    @Override
    public List getFinanceServiceFeeRate(Map info) {
        Map params = Maps.newHashMap();
        String sql="SELECT a.*,b.许可_LSH dealer  from (select a.b_region_code  region ,a.b_region_code, a.pma_code ,a.pma_code pma, a.permit BMBS_permit,   ROUND( IFNULL(a.financial_service_fee_amount / a.financial_loan_amount ,0),4) *100  dealerServicefeeRatio from (\n" +
                "\t\t\tSELECT   a.b_region ,a.b_region_code, a.pma_code ,a.pma, a.permit,\n" +
                "\t\t\t\t\t\tsum(a.financial_service_fee_amount * 1.06) financial_service_fee_amount ,\n" +
                "\t\t\t\t\t\tsum(a.financial_loan_amount) financial_loan_amount    \n" +
                "\t\t\tfrom   t_kpi_year_monthly_data  a   \n" +
                "\t\t\tWHERE   a.stat_date>=:startDate and a.stat_date<=:endDate    and permit <>'null'     \n" +
                "\t\t\t\t\t\tand  2=2  GROUP BY  a.permit\n" +
                " ) a ) a    LEFT JOIN lsh经销商表 b ON a.BMBS_permit=许可_BMBS   where  1=1 ";
        if(info.containsKey("beginRatio")){
            sql=sql+"and  a.dealerServicefeeRatio>=:beginRatio  ";
            params.put("beginRatio",info.get("beginRatio"));
        }
        if(info.containsKey("endRatio")){
            sql=sql+" and  a.dealerServicefeeRatio<=:endRatio ";
            params.put("endRatio",info.get("endRatio"));
        }
        if(!"LSH".equalsIgnoreCase(String.valueOf(info.get("permit")))){
            sql= sql.replace("2=2","(a.b_region=:permit or a.b_region_code =:permit or a.pma=:permit or a.pma_code=:permit or a.permit=:permit  or group_code=:permit)");
            params.put("permit",info.get("permit"));
        }
        params.put("startDate",info.get("startDate"));
        params.put("endDate",info.get("endDate"));
        return this.getList(sql, params);
    }

    @Override
    public List getFinanceServiceFeeRateForDCH(Map info) {
        Map params = Maps.newHashMap();
        String sql="SELECT a.* from (select a.b_region_code  region ,a.b_region_code, a.pma_code ,a.pma, a.permit dealer,   ROUND( IFNULL(a.financial_service_fee_amount / a.financial_load_amount ,0),4) *100  dealerservicefeeratio from (\n" +
                "\t\t\tSELECT   a.b_region ,a.b_region_code, a.pma_code ,a.pma, a.permit,\n" +
                "\t\t\t\t\t\tsum(a.financial_service_fee_amount) financial_service_fee_amount ,\n" +
                "\t\t\t\t\t\tsum(a.financial_load_amount) financial_load_amount    \n" +
                "\t\t\tfrom   t_bi_monthly_stat_data  a   \n" +
                "\t\t\tWHERE   a.stat_date>=:startDate and a.stat_date<=:endDate    and permit <>'null'     \n" +
                "\t\t\t\t\t\tand  2=2  GROUP BY  a.permit\n" +
                ") a ) a  where  1=1 ";
        if(info.containsKey("beginRatio")){
            sql=sql+"and  a.dealerServicefeeRatio>=:beginRatio  ";
            params.put("beginRatio",info.get("beginRatio"));
        }
        if(info.containsKey("endRatio")){
            sql=sql+" and  a.dealerServicefeeRatio<=:endRatio ";
            params.put("endRatio",info.get("endRatio"));
        }
        if(!"DCH".equalsIgnoreCase(String.valueOf(info.get("permit")))){
            sql= sql.replace("2=2","(a.b_region=:permit or a.b_region_code =:permit or a.pma=:permit or a.pma_code=:permit or a.permit=:permit  or group_code=:permit)");
            params.put("permit",info.get("permit"));
        }
        params.put("startDate",info.get("startDate"));
        params.put("endDate",info.get("endDate"));
        return this.getList(sql, params);
    }

    @Override
    public List getFinanceServiceFeeRatePMA(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.pma_code ,a.pma_code pma, ROUND( IFNULL(a.financial_service_fee_amount / a.financial_loan_amount ,0),4) *100 financialServiceFeeRate from (\n" +
                "\t\t\tSELECT a.pma_code ,a.pma, \n" +
                "\t\t\t\t\t\tsum(a.financial_service_fee_amount * 1.06) financial_service_fee_amount ,\n" +
                "\t\t\t\t\t\tsum(a.financial_loan_amount) financial_loan_amount    \n" +
                "\t\t\tfrom t_kpi_year_monthly_data a   \n" +
                "\t\t\tWHERE a.stat_date>=:startDate and a.stat_date<=:endDate  \n" +
                "\t\t\t\t\t\t\tand b_region is not null and b_region <>'null' GROUP BY  a.pma\n" +
                ") a";
        params.put("startDate",info.get("startDate"));
        params.put("endDate",info.get("endDate"));
        return this.getList(sql, params);
    }

    @Override
    public List getFinanceServiceFeeRateRegion(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.b_region  region ,a.b_region_code,  ROUND( IFNULL(a.financial_service_fee_amount / a.financial_loan_amount ,0),4) *100 financialServiceFeeRate from (\n" +
                "\t\t\tSELECT   a.b_region ,a.b_region_code, \n" +
                "\t\t\t\t\t\tsum(a.financial_service_fee_amount * 1.06) financial_service_fee_amount ,\n" +
                "\t\t\t\t\t\tsum(a.financial_loan_amount) financial_loan_amount    \n" +
                "\t\t\tfrom   t_kpi_year_monthly_data  a   \n" +
                "\t\t\tWHERE a.stat_date>=:startDate and a.stat_date<=:endDate  \n" +
                "\t\t\t\t\t\t\tand b_region is not null and b_region <>'null' GROUP BY  a.b_region\n" +
                ") a ";
        params.put("startDate",info.get("startDate"));
        params.put("endDate",info.get("endDate"));
        return this.getList(sql, params);
    }

    @Override
    public List getFinanceServiceFeeRateRegionForDCH(Map info) {
        Map params = Maps.newHashMap();
        String sql="select a.b_region  region ,a.b_region_code,  ROUND( IFNULL(a.financial_service_fee_amount / a.financial_load_amount ,0),4) *100 financialServiceFeeRate from (\n" +
                "\t\t\tSELECT   a.b_region ,a.b_region_code, \n" +
                "\t\t\t\t\t\tsum(a.financial_service_fee_amount) financial_service_fee_amount ,\n" +
                "\t\t\t\t\t\tsum(a.financial_load_amount) financial_load_amount    \n" +
                "\t\t\tfrom   t_bi_monthly_stat_data  a   \n" +
                "\t\t\tWHERE a.stat_date>=:startDate and a.stat_date<=:endDate  \n" +
                "\t\t\t\t\t\t\tand b_region is not null and b_region <>'null' GROUP BY  a.b_region\n" +
                ") a ";
        params.put("startDate",info.get("startDate"));
        params.put("endDate",info.get("endDate"));
        return this.getList(sql, params);
    }

    @Override
    public PageData getMargetingActivityList(String area, String pma, String dealer, String series, String lead, String selBrand,
                                             String level, String staDate, String endDate, String export, int pageIndex, int pageSize) {
        StringBuffer sql = new StringBuffer();
        Map params = new HashMap();
        sql.append(" SELECT ");
        sql.append("    活动编号, date(创建日期) as 创建日期,活动名称,主导与发起,活动类型,主推车系, ");
        sql.append("    date(开始日期) as 开始日期,date(结束日期) as 结束日期 ,经销商名,CTA,大区,状态 ");
        sql.append(" FROM `vw_group_report_activity_list` ");
        sql.append(" WHERE 1 = 1 ");
//        if (StringUtils.isNotNullOrBlank(area)) {
//            sql.append(" AND 大区 = :area ");
//            params.put("area", area);
//        }
        if (StringUtils.isNotNullOrBlank(area)) {

            sql.append(" AND CTA = :area ");
            params.put("area", area);
        }
        if (StringUtils.isNotNullOrBlank(dealer)) {
            sql.append(" AND 经销商名 = :dealer ");
            params.put("dealer", dealer);
        }
        if (StringUtils.isNotNullOrBlank(series)) {
            sql.append(" AND 活动类型 = :series ");
            params.put("series", series);
        }
        if (StringUtils.isNotNullOrBlank(lead)) {
            sql.append(" AND 主导与发起 = :lead ");
            params.put("lead", lead);
        }
        if (StringUtils.isNotNullOrBlank(dealer)) {
            sql.append(" AND 品牌 LIKE :selBrand ");
            params.put("selBrand", "%" + selBrand + "%");
        }
        if (StringUtils.isNotNullOrBlank(level)) {
            sql.append(" AND 车系 = :level ");
            params.put("level", "%" + level + "%");
        }
        if (StringUtils.isNotNullOrBlank(staDate)) {
            sql.append(" AND 开始日期 >= :staDate ");
            params.put("staDate", staDate);
        }
        if (StringUtils.isNotNullOrBlank(endDate)) {
            sql.append(" AND 结束日期 <= :endDate ");
            params.put("endDate", endDate);
        }
        sql.append(" ORDER BY 创建日期 DESC ");
        return this.getPage(sql.toString(), params, pageSize, pageIndex);
    }

    @Override
    public List getMargetingActivityKPIData(String staDate, String endDate, String export) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append("    b.`活动编号`,a.act_id,a.kpi_id, ");
        sql.append("    IF(c.`KPI编号` IS NULL,0,a.kpi_value) kpi_value, ");
        sql.append("    CASE ");
        sql.append("        WHEN c.`选择状态` = '必选' THEN '活动必选KPI' ");
        sql.append("        WHEN c.`选择状态` = '可选' THEN '活动可选KPI' ");
        sql.append("        ELSE '未启用KPI' ");
        sql.append("    end as 活动状态, ");
        sql.append("    d.KPI ");
        sql.append(" FROM t_group_report_act_info a ");
        sql.append(" JOIN `lsh活动表` b ON a.act_id = b.`活动编号` ");
        sql.append(" LEFT JOIN `lsh活动类型附属` c ON b.`活动类型编号` = c.`活动类型编号` AND a.kpi_id=c.`KPI编号` ");
        sql.append(" left JOIN  vw_group_report_activity_kpi d on d.`KPI编号`= a.kpi_id ");
        sql.append(" WHERE (b.开始日期 <= :endDate or b.结束日期 >= :staDate) ");
        if ("1".equals(export)) {
            sql.append(" AND c.`选择状态` = '必选' ");
        }
        Map params = new HashMap();
        params.put("staDate", staDate);
        params.put("endDate", endDate);
        return this.getList(sql.toString(), params);
    }

    @Override
    public List getSalesFinanceReoprt(Map info){
        Map params = Maps.newHashMap();
        String sql="SELECT\n" +
                "\t(@i \\:=@i + 1) AS num,\n" +
                "\ta.*\n" +
                "FROM\n" +
                "\tvw_dealer_report_sale_finance_data a,\n" +
                "\t(SELECT @i \\:= 0) AS it\n" +
                "WHERE\n" +
                "\t a.invoiceDate <= :endDate \n" +
                "AND a.invoiceDate >= :startDate\n";
        if(!"admin".equalsIgnoreCase(info.get("fr_username").toString())){
            sql += " AND a.dealerCode = (\n" +
                    "\tSELECT\n" +
                    "\t\ta.许可\n" +
                    "\tFROM\n" +
                    "\t\t`用户` a\n" +
                    "\tWHERE\n" +
                    "\t\ta.`用户名` = :fr_username\n" +
                    "\tAND a.删除状态 <> '已删除'\n" +
                    ")\n" ;
            params.put("fr_username",info.get("fr_username").toString());
        }
        sql +=  " ORDER BY\n" +
                "\ta.invoiceDate DESC";
        params.put("startDate",info.get("startDate").toString());
        params.put("endDate",info.get("endDate").toString());
        return this.getList(sql, params);
    }
}
